# -*- coding: utf-8 -*-
"""
author : heting
date : 2018/5/30 5:44
desc : 
"""
from MyComFiles.MyPublic import *

engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/qy_datasys?charset=utf8', echo=False)

os.chdir('./')
workPath = os.getcwd()
print('workPath: ', os.getcwd())
time_start = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

sql = """
        -- 9查看全部进件的记录
        -- 
        SELECT
            t.id,
            YEAR (t.created_at) 'year',
            DATE_FORMAT(t.created_at, '%Y-%m') 'month',
            date(t.created_at) date,
            t.approval_jinjian_id,
            t.created_at 'time',
            t.approval_status,
            t.approval_name,
            t.approval_type,
            t.approval_desc
        FROM
            qy_approval_prod.approval_logs t
        WHERE
            t.approval_jinjian_id NOT IN ('')
        and date(t.created_at)>='2018-04-01'
        AND (t.approval_status IN (
            '用户完成进件',
            '面签提交',
            '面签撤销',
            '面签抢单',
            '初审通过',
            '初审拒绝',
            '初审抢单',
            '终审抢单',
            '终审拒绝',
            '终审通过',
            '用户拒绝',
            '用户接受',
            '客服抢单'
        )
        or t.approval_status like '%流转%')
        ORDER BY
            t.approval_jinjian_id,
            t.id;
"""
df = pdReadSql(sql)

"""匹配中介机构"""
sql = """
        -- 查找中介机构名称
        -- 
        SELECT            
            t.jinjian_id approval_jinjian_id,
            t.agency_name  
        FROM
            qy_approval_prod.approval_orders t
"""
tempDf = pdReadSql(sql)
df = pd.merge(df,tempDf,on='approval_jinjian_id',how='left')

"""处理人员姓名"""
df['approval_name'] = df['approval_name'].map(lambda x:x.replace('面签','').replace('初审','').replace('终审','').replace('客服',''))
df['approval_name'] = df['approval_type']+df['approval_name']

"""设置层次化索引"""
#df.index = [list(df['approval_jinjian_id']),df.index]
"""dataframe 分组 按照时间排序升序 key定义在sortAscendSe中"""
df = df.groupby([df['approval_jinjian_id'],df['approval_type']]).apply(sortSe_n)

"""再次对df 进行分组排序 分组key为进件id  排序key 为time"""
df = df.groupby([df['approval_jinjian_id']]).apply(sortAscendSe)


"""保存excel表格"""
def saveToExcel(saveName,df,workPath,index=False,type='table'):
    path = newSaveName(saveName,workPath,type)
    df.to_excel(path,index=index)

def newSaveName(saveName,workPath,type):
    if type != 'table':
        path = 'static/xlsx/%s.xlsx'%saveName
        workPath = os.path.dirname(workPath)
        print(workPath)
    else:
        path = 'table/%s.xlsx'%saveName
    saveName = os.path.join(workPath,path)
    if not os.path.exists(saveName):
        os.makedirs(saveName)
    return saveName

"""回退上级目录"""
#workPath = os.path.dirname(workPath)
saveName = 'orderDetail'
saveToExcel(saveName,df,workPath,index=True,type='static')

"""对审核人员进行作业统计"""
df = df['approval_jinjian_id'].groupby([df['month'],df['approval_name']],group_keys=False).count()
saveName = 'orderData'
#saveToExcel(saveName,df,workPath,index=True)

dataList = []
for i in range(len(df.index)):
    item = df.index[i]
    semiDict = {}
    semiDict['month'] = item[0]
    semiDict['name'] = item[1]
    semiDict['type'] = item[1][:2]
    semiDict['orderCnt'] = df.values[i]
    semiDict['updateTime'] = time_start
    dataList.append(semiDict)

df = DataFrame(dataList)
"""series 倒序或者升序(最大/小的前n)取n"""
def sortSe_2(x):
    return x.sort_values(by='orderCnt',ascending = True)

df = df.groupby([df['month'],df['type']]).apply(sortSe_2)
df.to_sql('order_per_cnt', engine,if_exists='replace',index=False,dtype={'orderCnt':Integer()})


#name = list(df['name'][df['month']=='2018-05'])
#print('name= ',name)
#cnt = list(df['orderCnt'][df['month']=='2018-05'])
#print('cnt= ',cnt)
#%%
saveName = 'order_per_cnt'
saveToExcel(saveName,df,workPath,index=True,type='static')
time_end = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print('开始时间:', time_start)
print('结束时间:', time_end)